﻿CREATE PROCEDURE [acms].[Post_GetAllByParentGuidAndTopicId]
	@ParentGuid uniqueidentifier,
	@TopicId int,
	@SiteVersion DateTime,
	@IncludeChildren bit,
	@StartIndex int,
	@PageSize int,
	@TotalRecordsOut int Output
AS
	declare @tmpVersion DateTime
	
	set @tmpVersion = acms.GetPublishDate(@SiteVersion)
	
	if(@tmpVersion is null and @SiteVersion is not null)
		return;

	declare @tmp table (Id int, RowNumber int identity(1,1))

	if @IncludeChildren = 0 begin
		insert into @tmp
			(ID)
		SELECT a.Id
		FROM [Post_View] a
			inner join [PostTopic_View] b on a.Id = b.PostId
			inner join PostPositions c on a.Id = c.PostId and isNull(c.PublishDate, '9999/12/31') = isNull(@tmpVersion, '9999/12/31')
		Where a.ParentGuid = @ParentGuid
			and b.TopicId = @TopicId
		ORDER By a.Sequence
	end
	else begin
		declare @ParentPath varchar(255)
		select @ParentPath = b.ParentPath + a.Name collate database_default + '%' collate database_default
			From Post_View a
				inner join PostPositions b on a.Id = b.PostId and isNull(b.PublishDate, '9999/12/31') = isNull(@tmpVersion, '9999/12/31')
			Where a.PostGuid = @ParentGuid
		
		insert into @tmp
			(Id)
		SELECT a.Id
		FROM [Post_View] a
			inner join [PostTopic_View] b on a.Id = b.PostId
			inner join PostPositions c on a.Id = c.PostId and isNull(c.PublishDate, '9999/12/31') = isNull(@tmpVersion, '9999/12/31')
		Where c.ParentPath Like @ParentPath
			and b.TopicId = @TopicId
		ORDER By c.ParentSequence, a.Sequence
	end
	
	if @PageSize = 0 begin
		select b.*, c.ParentPath, c.PublishDate as SiteVersion 
		from @tmp a
			inner join [Post_View] b on a.Id = b.Id
			inner join PostPositions c on a.Id = c.PostId and isNull(c.PublishDate, '9999/12/31') = isNull(@tmpVersion, '9999/12/31')
		order by a.RowNumber
		set @TotalRecordsOut = @@RowCount
	end
	else begin
		select b.*, c.ParentPath, c.PublishDate as SiteVersion 
		from @tmp a
			inner join [Post_View] b on a.Id = b.Id
			inner join PostPositions c on a.Id = c.PostId and isNull(c.PublishDate, '9999/12/31') = isNull(@tmpVersion, '9999/12/31')
		where a.RowNumber between @StartIndex + 1 and @StartIndex + @PageSize
		order by a.RowNumber
		
		select @TotalRecordsOut = Count(Id) From @tmp
	end

RETURN 0;